IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GenAGUpdTriggers' AND type = 'P')
     DROP PROCEDURE dbo.GenAGUpdTriggers
GO

CREATE PROCEDURE dbo.GenAGUpdTriggers
     @ExcludeTableList varchar(8000) = NULL,
     @DropNoCreateFlag tinyint       = 0,
     @DropAllAGFlag    tinyint       = 0
AS
/*
 *
 * GenAGUpdTriggers
 *
 * Purpose:      To generate triggers used to capture data changes to tables
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      Triggers are dropped, and/or recreated
 *
 * Returns:      
 *
 * Note(s):      This procedure tries as hard as possible to not deal with any
 *               data in a row-by-row methodology or as Jeff Moden calls it RBAR.
 *               We do three loops, in the first two loops we work our way through
 *               each column ordinal id, and build up the commands one column at
 *               a time, I attempted other methods but found this one to work the
 *               best.  Those loops are basically key fields work, and then 
 *               everything else.  The last loop actually execute the fully built
 *               code against the DB.  I could have generated one ginormous SQL
 *               command but had I done that then if there was a problem with one
 *               view then they would fail from then on.  I would rather have all
 *               the others compile and just deal with the one that didn't.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/02/2009, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     -- Declare local variables
     DECLARE @NOW                    datetime,
             @AlwaysExcludeTableList varchar(8000)

     -- Set initial working values.
     SELECT @NOW                    = GETDATE(),
            @AlwaysExcludeTableList = dbo.GetCfgCtrlKeyAsVarchar8000('GenAGUpdTriggers_AlwaysExclude')

     -- Handle drop all action
     IF ( @DropAllAGFlag = 1 )
     BEGIN
          DECLARE @DropAllCode nvarchar(max)

          SELECT @DropAllCode = ''

          SELECT @DropAllCode = @DropAllCode
                              + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''['
                              + s.name
                              + '].['
                              + o.name
                              + ']'', N''TR'')'
                              + ' ) DROP TRIGGER ['
                              + s.name
                              + '].['
                              + o.name
                              + '];'
                              + CHAR(13)+ CHAR(10)
          FROM   sys.extended_properties ep INNER JOIN sys.objects o 
                    ON ep.major_id = o.object_id 
                 INNER JOIN sys.schemas s
                    ON o.schema_id = s.schema_id 
          WHERE  ep.name = 'GenAGUpdTriggers'

          PRINT 'Dropping all auto-generated triggers'          
          EXEC ( @DropAllCode )
          
          RETURN     
     END

     -- Get Table List
     DECLARE @Table TABLE ( TABLE_CATALOG    varchar(128) NOT NULL,
                            TABLE_SCHEMA     varchar(128) NOT NULL,
                            TABLE_NAME       varchar(128) NOT NULL )

     INSERT @Table 
          ( TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME )
     SELECT DISTINCT
            t.TABLE_CATALOG,
            t.TABLE_SCHEMA,
            t.TABLE_NAME
     FROM   INFORMATION_SCHEMA.TABLES t
     WHERE  t.TABLE_TYPE = 'BASE TABLE'
     AND    t.TABLE_NAME NOT IN ( 'sysdiagrams' )
     -- We handle the exclusions here, since we do none of the later processes
     -- will try to build for these tables.  We do this twice, once for always
     -- excluded tables and another time for ones that were specified for this
     -- run.  It is better overall to write them to the CfgCtrl table so that
     -- they are always excluded, but if you just need to exclude an extra one
     -- then specifying it is fine.
     AND    NOT EXISTS ( SELECT 'X'
                         FROM   dbo.BreakListToVarcharByDelimeter( @AlwaysExcludeTableList, ',' ) e1
                         WHERE  e1.BrokenList = t.TABLE_NAME )
     AND    NOT EXISTS ( SELECT 'X'
                         FROM   dbo.BreakListToVarcharByDelimeter( @ExcludeTableList, ',' ) e2
                         WHERE  e2.BrokenList = t.TABLE_NAME )

     -- Get Keys
     DECLARE @Key TABLE ( CONSTRAINT_CATALOG varchar(128) NOT NULL,
                          CONSTRAINT_SCHEMA  varchar(128) NOT NULL,
                          TABLE_NAME         varchar(128) NOT NULL,
                          COLUMN_NAME        varchar(128) NOT NULL,
                          ORDINAL_POSITION   int          NOT NULL )

     INSERT @Key 
          ( CONSTRAINT_CATALOG,
            CONSTRAINT_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            ORDINAL_POSITION )
     SELECT kcu.CONSTRAINT_CATALOG,
            kcu.CONSTRAINT_SCHEMA,
            kcu.TABLE_NAME,
            kcu.COLUMN_NAME,
            kcu.ORDINAL_POSITION
     FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu INNER JOIN @Table t
               ON kcu.TABLE_CATALOG = t.TABLE_CATALOG 
              AND kcu.TABLE_SCHEMA  = t.TABLE_SCHEMA 
              AND kcu.TABLE_NAME    = t.TABLE_NAME 
     WHERE  NOT EXISTS ( SELECT 'X' -- Exclude DRI
                         FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
                         WHERE  rc.CONSTRAINT_CATALOG = kcu.CONSTRAINT_CATALOG 
                         AND    rc.CONSTRAINT_SCHEMA  = kcu.CONSTRAINT_SCHEMA 
                         AND    rc.CONSTRAINT_NAME    = kcu.CONSTRAINT_NAME )
     AND    kcu.TABLE_NAME NOT IN ( 'sysdiagrams' )
     ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME, ORDINAL_POSITION

     -- Get column list for tables
     DECLARE @Column TABLE ( TABLE_CATALOG    varchar(128) NOT NULL,
                             TABLE_SCHEMA     varchar(128) NOT NULL,
                             TABLE_NAME       varchar(128) NOT NULL,
                             COLUMN_NAME      varchar(128) NOT NULL,
                             ORDINAL_POSITION int          NOT NULL,
                             IS_NULLABLE      varchar(10)  NOT NULL,
                             IsKeyFieldFlag   tinyint      NOT NULL DEFAULT 0,
                             LastColumnFlag   tinyint      NOT NULL DEFAULT 0 )

     INSERT @Column 
          ( TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            COLUMN_NAME,
            ORDINAL_POSITION,
            IS_NULLABLE )
     SELECT c.TABLE_CATALOG,
            c.TABLE_SCHEMA,
            c.TABLE_NAME,
            c.COLUMN_NAME,
            c.ORDINAL_POSITION,
            c.IS_NULLABLE
     FROM   INFORMATION_SCHEMA.COLUMNS c INNER JOIN @Table t
               ON c.TABLE_CATALOG = t.TABLE_CATALOG 
              AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA 
              AND c.TABLE_NAME    = t.TABLE_NAME 
     ORDER BY c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION

     -- Figure out key columns, if it exists in keys it is a key column..
     UPDATE @Column 
     SET    IsKeyFieldFlag = 1
     FROM   @Column c INNER JOIN @Key k
               ON c.TABLE_CATALOG = k.CONSTRAINT_CATALOG 
              AND c.TABLE_SCHEMA  = k.CONSTRAINT_SCHEMA 
              AND c.TABLE_NAME    = k.TABLE_NAME 
              AND c.COLUMN_NAME   = k.COLUMN_NAME 

     -- Figure out last column
     UPDATE @Column
     SET    LastColumnFlag = 1
     FROM   @Column c,
            ( SELECT TABLE_CATALOG, 
                     TABLE_SCHEMA, 
                     TABLE_NAME, 
                     MAXORDINAL = MAX(ORDINAL_POSITION ) 
              FROM   @Column
              GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) AS cm
     WHERE  c.TABLE_CATALOG    = cm.TABLE_CATALOG 
     AND    c.TABLE_SCHEMA     = cm.TABLE_SCHEMA 
     AND    c.TABLE_NAME       = cm.TABLE_NAME 
     AND    c.ORDINAL_POSITION = cm.MAXORDINAL 

     -- Start building trigger artifacts
     DECLARE @TB TABLE ( RecId              int IDENTITY(1,1) NOT NULL,
                         TABLE_CATALOG      varchar(128)      NOT NULL,
                         TABLE_SCHEMA       varchar(128)      NOT NULL,
                         TABLE_NAME         varchar(128)      NOT NULL,
                         TriggerName        varchar(128)      NOT NULL,
                         AllColumnKeyFlag   tinyint           NOT NULL DEFAULT 0,
                         KeyFieldWHERE      varchar(max)      NOT NULL DEFAULT '',
                         TblDeclareStmt     varchar(max)      NOT NULL DEFAULT '',
                         InsTblStmt         varchar(max)      NOT NULL DEFAULT '',
                         InsTblSelectStmt   varchar(max)      NOT NULL DEFAULT '',
                         XMLKeyFieldStmt    varchar(max)      NOT NULL DEFAULT '',
                         XMLNonKeyFieldStmt varchar(max)      NOT NULL DEFAULT '',
                         DiffCode           varchar(max)      NOT NULL DEFAULT '',
                         KeyChgInsCode      varchar(max)      NOT NULL DEFAULT '',
                         KeyChgDelCode      varchar(max)      NOT NULL DEFAULT '',
                         LMDBaseStmt        varchar(max)          NULL,
                         LMDFullStmt        varchar(max)          NULL,
                         DropTriggerCode    varchar(max)          NULL,
                         FullTriggerCode    varchar(max)          NULL,
                         ExtendedPropCode   varchar(max)          NULL )

     INSERT @TB
          ( TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            TriggerName )
     SELECT TABLE_CATALOG,
            TABLE_SCHEMA,
            TABLE_NAME,
            TriggerName = 'trUpd'
                        + TABLE_NAME
                        + '_AG'
     FROM   @Table 

     -- Determine if all fields are key, if they are we won't generate the field diff
     -- logic later.
     UPDATE @TB 
     SET    AllColumnKeyFlag = 1
     FROM   @TB t
     WHERE  NOT EXISTS ( SELECT 'X'
                         FROM   @Column c
                         WHERE  c.TABLE_CATALOG  = t.TABLE_CATALOG 
                         AND    c.TABLE_SCHEMA   = t.TABLE_SCHEMA 
                         AND    c.TABLE_NAME     = t.TABLE_NAME
                         AND    c.IsKeyFieldFlag = 0 )

     -- Build LMD Base Statement where applicable.
     UPDATE @TB
     SET    LMDBaseStmt = '     UPDATE ['
                        + t.TABLE_SCHEMA
                        + '].['
                        + t.TABLE_NAME 
                        + '] '
                        + CHAR(13) + CHAR(10)
                        + '     SET    [LastModDt] = CURRENT_TIMESTAMP '
                        + CHAR(13) + CHAR(10)
                        + '     FROM   [' 
                        + t.TABLE_SCHEMA
                        + '].['
                        + t.TABLE_NAME 
                        + '] d, '
                        + CHAR(13) + CHAR(10)
                        + '            inserted i '
                        + CHAR(13) + CHAR(10)
                        + '     WHERE  '
     FROM   @TB t
     WHERE  EXISTS ( SELECT 'X'
                     FROM   @Column c
                     WHERE  t.TABLE_CATALOG = c.TABLE_CATALOG 
                     AND    t.TABLE_SCHEMA  = c.TABLE_SCHEMA 
                     AND    t.TABLE_NAME    = c.TABLE_NAME 
                     AND    c.COLUMN_NAME   = 'LastModDt' )

     -- Build out key where section
     DECLARE @MaxOrdinal int,
             @WkOrdinal  int

     SELECT @MaxOrdinal = MAX(ORDINAL_POSITION) FROM @Key 
     SET @WkOrdinal = 1

     -- Sorry Jeff..
     WHILE ( @WkOrdinal <= @MaxOrdinal )
     BEGIN
          UPDATE @TB
          SET    KeyFieldWHERE   = KeyFieldWHERE
                                 + CASE WHEN @WkOrdinal = 1
                                        THEN 'i.['
                                        ELSE CHAR(13) + CHAR(10)
                                           + '     AND    i.['
                                   END
                                 + k.COLUMN_NAME 
                                 + '] = d.['
                                 + k.COLUMN_NAME 
                                 + ']',
                 XMLKeyFieldStmt = XMLKeyFieldStmt
                                 + '                  + '''
                                 + k.COLUMN_NAME 
                                 + '="'''
                                 + CHAR(13) + CHAR(10)
                                 + '                  + REPLACE( CONVERT( varchar(8000), i.['
                                 + k.COLUMN_NAME 
                                 + '] ), ''"'', '''''''' )'
                                 + CHAR(13) + CHAR(10)
                                 + '                  + ''" '''
                                 + CHAR(13) + CHAR(10)
          FROM   @TB t INNER JOIN @Key k 
                    ON t.TABLE_CATALOG = k.CONSTRAINT_CATALOG 
                   AND t.TABLE_SCHEMA  = k.CONSTRAINT_SCHEMA 
                   AND t.TABLE_NAME    = k.TABLE_NAME 
          WHERE  k.ORDINAL_POSITION = @WkOrdinal 

          SELECT @WkOrdinal = @WkOrdinal + 1
     END

     -- Build out full field sections
     SELECT @MaxOrdinal = MAX(ORDINAL_POSITION) FROM @Column 
     SET @WkOrdinal = 1

     -- Sorry Jeff..
     WHILE ( @WkOrdinal <= @MaxOrdinal )
     BEGIN
          UPDATE @TB
          SET    TblDeclareStmt     = TblDeclareStmt
                                    + CASE WHEN @WkOrdinal = 1
                                           THEN '     DECLARE @TABLE TABLE ( ['
                                           ELSE '                            ['
                                      END
                                    + c.COLUMN_NAME 
                                    + '] tinyint NULL'
                                    + CASE WHEN c.LastColumnFlag = 1
                                           THEN ' )'
                                           ELSE ','
                                      END
                                    + CHAR(13) + CHAR(10),
                 InsTblStmt         = InsTblStmt
                                    + CASE WHEN @WkOrdinal = 1
                                           THEN '     INSERT @TABLE'
                                              + CHAR(13) + CHAR(10)
                                              + '          ( ['
                                           ELSE '            ['
                                      END
                                    + c.COLUMN_NAME 
                                    + CASE WHEN c.LastColumnFlag = 1
                                           THEN '] )'
                                           ELSE '],'
                                      END
                                    + CHAR(13) + CHAR(10),
                 InsTblSelectStmt   = InsTblSelectStmt
                                    + CASE WHEN @WkOrdinal = 1
                                           THEN '     SELECT CASE WHEN SUBSTRING( @CU, '
                                           ELSE '            CASE WHEN SUBSTRING( @CU, '
                                      END
                                    + CONVERT( varchar(3), ( ( c.ORDINAL_POSITION - 1 ) / 8 ) + 1 )
                                    + ', 1 ) & POWER(2, '
                                    + CONVERT( varchar(3), c.ORDINAL_POSITION - ( ( ( c.ORDINAL_POSITION - 1 ) / 8 ) * 8 ) - 1 )
                                    + ') > 0 THEN 1 ELSE 0 END'
                                    + CASE WHEN c.LastColumnFlag = 1
                                           THEN ''
                                           ELSE ','
                                      END
                                    + CHAR(13) + CHAR(10),
                 XMLNonKeyFieldStmt = XMLNonKeyFieldStmt
                                    + CASE WHEN c.IsKeyFieldFlag = 0
                                           THEN '                  + CASE WHEN t.['
                                              + c.COLUMN_NAME 
                                              + '] = 1 '
                                              + CHAR(13) + CHAR(10)
                                              + CASE WHEN c.IS_NULLABLE = 'NO'
                                                     THEN '                          AND i.['
                                                        + c.COLUMN_NAME 
                                                        + '] != d.['
                                                        + c.COLUMN_NAME 
                                                        + ']'
                                                        + CHAR(13) + CHAR(10)
                                                     ELSE '                          AND ( i.['
                                                        + c.COLUMN_NAME 
                                                        + '] != d.['
                                                        + c.COLUMN_NAME 
                                                        + ']'
                                                        + CHAR(13) + CHAR(10)
                                                        + '                             OR ( i.['
                                                        + c.COLUMN_NAME 
                                                        + '] IS     NULL AND d.['
                                                        + c.COLUMN_NAME 
                                                        + '] IS NOT NULL )'
                                                        + CHAR(13) + CHAR(10)
                                                        + '                             OR ( i.['
                                                        + c.COLUMN_NAME 
                                                        + '] IS NOT NULL AND d.['
                                                        + c.COLUMN_NAME 
                                                        + '] IS     NULL )'
                                                        + CHAR(13) + CHAR(10)
                                                        + '                              )'
                                                        + CHAR(13) + CHAR(10)
                                                END
                                              + '                         THEN ''Pre_'
                                              + c.COLUMN_NAME 
                                              + '="'' + '
                                              + CASE WHEN c.IS_NULLABLE = 'NO'
                                                     THEN ''
                                                     ELSE 'ISNULL( '
                                                END
                                              + 'REPLACE( CONVERT( varchar(8000), d.['
                                              + c.COLUMN_NAME 
                                              + '] ), ''"'', '''''''' )'
                                              + CASE WHEN c.IS_NULLABLE = 'NO'
                                                     THEN ''
                                                     ELSE ', ''[NULL]'' )'
                                                END
                                              + CHAR(13) + CHAR(10)
                                              + '                            + ''" Post_'
                                              + c.COLUMN_NAME 
                                              + '="'' + '
                                              + CASE WHEN c.IS_NULLABLE = 'NO'
                                                     THEN ''
                                                     ELSE 'ISNULL( '
                                                END
                                              + 'REPLACE( CONVERT( varchar(8000), i.['
                                              + c.COLUMN_NAME 
                                              + '] ), ''"'', '''''''' )'
                                              + CASE WHEN c.IS_NULLABLE = 'NO'
                                                     THEN ''
                                                     ELSE ', ''[NULL]'' )'
                                                END
                                              + CHAR(13) + CHAR(10)
                                              + '                            + ''" '''
                                              + CHAR(13) + CHAR(10)
                                              + '                         ELSE '''''
                                              + CHAR(13) + CHAR(10)
                                              + '                    END'
                                              + CHAR(13) + CHAR(10)
                                           ELSE ''
                                      END,
                 KeyChgInsCode      = KeyChgInsCode
                                    + '                  + '''
                                    + c.COLUMN_NAME 
                                    + '="'''
                                    + CHAR(13) + CHAR(10)
                                    + '                  + '
                                    + CASE WHEN c.IS_NULLABLE = 'NO'
                                           THEN ''
                                           ELSE 'ISNULL( '
                                      END
                                    + 'REPLACE( CONVERT( varchar(8000), i.['
                                    + c.COLUMN_NAME 
                                    + '] ), ''"'', '''''''' )'
                                    + CASE WHEN c.IS_NULLABLE = 'NO'
                                           THEN ''
                                           ELSE ', ''[NULL]'' )'
                                      END
                                    + CHAR(13) + CHAR(10)
                                    + '                  + ''" '''
                                    + CHAR(13) + CHAR(10),
                 KeyChgDelCode      = KeyChgDelCode
                                    + '                  + '''
                                    + c.COLUMN_NAME 
                                    + '="'''
                                    + CHAR(13) + CHAR(10)
                                    + '                  + '
                                    + CASE WHEN c.IS_NULLABLE = 'NO'
                                           THEN ''
                                           ELSE 'ISNULL( '
                                      END
                                    + 'REPLACE( CONVERT( varchar(8000), d.['
                                    + c.COLUMN_NAME 
                                    + '] ), ''"'', '''''''' )'
                                    + CASE WHEN c.IS_NULLABLE = 'NO'
                                           THEN ''
                                           ELSE ', ''[NULL]'' )'
                                      END
                                    + CHAR(13) + CHAR(10)
                                    + '                  + ''" '''
                                    + CHAR(13) + CHAR(10)
          FROM   @TB t INNER JOIN @Column c 
                    ON t.TABLE_CATALOG = c.TABLE_CATALOG 
                   AND t.TABLE_SCHEMA  = c.TABLE_SCHEMA 
                   AND t.TABLE_NAME    = c.TABLE_NAME 
          WHERE  c.ORDINAL_POSITION = @WkOrdinal 

          SELECT @WkOrdinal = @WkOrdinal + 1
     END

     -- Build out LMD Full Statement
     UPDATE @TB 
     SET    LMDFullStmt = LMDBaseStmt + KeyFieldWHERE 
     WHERE  LMDBaseStmt IS NOT NULL

     -- Build out DiffCode, handle all columns key case
     UPDATE @TB 
     SET    DiffCode      = CASE WHEN AllColumnKeyFlag = 1
                                 THEN ''
                                 ELSE '
     -- Populate log table, handles changed rows without key changes (normal case)
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [TraceText] )
     SELECT 1, -- Info
            '''
+ TriggerName 
+ ''', -- Trigger name
            '''', 
            '''
+ TABLE_NAME
+ ''', -- Table name
            ''Record Modification'',
            ''Non-Key Change'',
            ''XML'' = ''''
                  + ''<RecordChange ''
                  -- Key Field(s)
'
+ XMLKeyFieldStmt 
+'                  -- Non-Key Field(s)
'
+ XMLNonKeyFieldStmt 
+ '                  + ''/>''
     FROM   inserted i, 
            deleted  d,
            @TABLE   t -- Pseudo Cartesian table only has 1 row
     WHERE  '
+ KeyFieldWHERE 
                       END,
            KeyChgInsCode = '

     -- Handle inserted side of key change
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [MiscVC2],
            [TraceText] )
     SELECT 1, -- Info
            '''
+ TriggerName 
+ ''', -- Trigger name
            '''', 
            '''
+ TABLE_NAME
+ ''', -- Table name
            ''Record Modification'',
            ''Key Change'',
            ''Post Row'',
            ''XML'' = ''''
                  + ''<RecordChange ''
'
+ KeyChgInsCode
+ '                  + ''/>''
     FROM   inserted i
     WHERE  NOT EXISTS ( SELECT ''X''
                         FROM   deleted d
                         WHERE  '
+ REPLACE(KeyFieldWHERE, '     ', '                         ' )
+' )',
            KeyChgDelCode = '

     -- Handle delete side of key change
     INSERT [dbo].[EventLog]
          ( [EventLogTypeId],
            [LogByApp],
            [LogByAppSubSystem],
            [LogByDesc],
            [LogText],
            [MiscVC1],
            [MiscVC2],
            [TraceText] )
     SELECT 1, -- Info
            '''
+ TriggerName 
+ ''', -- Trigger name
            '''', 
            '''
+ TABLE_NAME
+ ''', -- Table name
            ''Record Modification'',
            ''Key Change'',
            ''Pre Row'',
            ''XML'' = ''''
                  + ''<RecordChange ''
'
+ KeyChgDelCode
+ '                  + ''/>''
     FROM   deleted d
     WHERE  NOT EXISTS ( SELECT ''X''
                         FROM   inserted i
                         WHERE  '
+ REPLACE(KeyFieldWHERE, '     ', '                         ' )
+' )'

     -- Build out full trigger
     UPDATE @TB
     SET    DropTriggerCode = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''['
                            + TABLE_SCHEMA
                            + '].['
                            + TriggerName 
                            + ']'', N''TR'') ) DROP TRIGGER ['
                            + TABLE_SCHEMA
                            + '].['
                            + TriggerName 
                            + '];',
            FullTriggerCode = '

CREATE TRIGGER ['
+ TriggerName 
+ '] ON ['
+ TABLE_SCHEMA
+ '].['
+ TABLE_NAME
+ '] FOR UPDATE
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
-- 
-- System generated trigger for changed data tracking.  To regenerate please run
-- GenAGUpdTriggers stored procedure
-- 
-- -----------------------------------------------------------------------------
-- -----------------------------------------------------------------------------
AS
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON
'
+ CASE WHEN LMDFullStmt IS NOT NULL 
       THEN CHAR(13) + CHAR(10)
          + '     -- Handle Last Mod Date Logic'
          + CHAR(13) + CHAR(10)
          + LMDFullStmt 
          + CHAR(13) + CHAR(10)
       ELSE ''
  END
+ '
     -- Get changed columns
     DECLARE @CU varbinary(255)
     SET @CU = COLUMNS_UPDATED()

     -- Declare table variable to hold flags indicating that that field changed
     -- in at least one record, we use this to cut the number of fields that the
     -- trigger must actually evaluate to determine whether there was a change.
'
+ TblDeclareStmt 
+ '
     -- Populate the table variable with flags indicating all fields that have 
     -- Changed in any record, this handles the single or multi row update case
'
+ InsTblStmt 
+ InsTblSelectStmt 
+ DiffCode 
+ KeyChgInsCode 
+ KeyChgDelCode 
+ '
END
'
     FROM   @TB

     -- Extended Property Code
     UPDATE @TB 
     SET    ExtendedPropCode = 'EXEC sys.sp_addextendedproperty '
                             + '@name = N''GenAGUpdTriggers'', @value = N'''
                             + CONVERT( varchar(10), @NOW, 101 ) 
                             + ' '
                             + CONVERT( varchar(8), @NOW, 108 )
                             + ''', '
                             + '@level0type = N''SCHEMA'', @level0name = ['
                             + TABLE_SCHEMA 
                             + '], @level1type = N''TABLE'', @level1name = ['
                             + TABLE_NAME 
                             + '], @level2type = N''TRIGGER'', @level2name = ['
                             + TriggerName 
                             + '] '
     FROM   @TB

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- We have the full trigger text built so no we are going to execute the 
     -- text against the database.
     --      
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     DECLARE @stmt   nvarchar(max),
             @msg    varchar(255),
             @retval int
     
     SELECT @MaxOrdinal = MAX(RecId) FROM @TB
     SET @WkOrdinal = 1

     WHILE ( @WkOrdinal <= @MaxOrdinal )
     BEGIN
          SELECT @stmt = DropTriggerCode 
          FROM   @TB 
          WHERE  RecId = @WkOrdinal 

          EXEC @retval = sp_executesql @stmt = @stmt
          
          IF ( @retval = 0 )
          BEGIN
               SELECT @msg = 'Drop Trigger: ['
                           + TriggerName 
                           + '] for Table: ['
                           + TABLE_SCHEMA 
                           + '].['
                           + TABLE_NAME 
                           + '] Successful.'
               FROM   @TB 
               WHERE  RecId = @WkOrdinal 

               PRINT @msg
          END
          ELSE
          BEGIN
               SELECT @msg = 'Drop Trigger: ['
                           + TriggerName 
                           + '] for Table: ['
                           + TABLE_SCHEMA 
                           + '].['
                           + TABLE_NAME 
                           + '] Was NOT Successful.'
               FROM   @TB 
               WHERE  RecId = @WkOrdinal 

               PRINT @msg
          END

          IF ( @DropNoCreateFlag = 0 )
          BEGIN
               -- Create Trigger
               SELECT @stmt = FullTriggerCode 
               FROM   @TB 
               WHERE  RecId = @WkOrdinal 

               EXEC @retval = sp_executesql @stmt = @stmt

               IF ( @retval = 0 )
               BEGIN
                    SELECT @msg = 'Trigger: ['
                                + TriggerName 
                                + '] for Table: ['
                                + TABLE_SCHEMA 
                                + '].['
                                + TABLE_NAME 
                                + '] Created Successfully.'
                    FROM   @TB 
                    WHERE  RecId = @WkOrdinal 

                    PRINT @msg
               END
               ELSE
               BEGIN
                    SELECT @msg = 'Trigger: ['
                                + TriggerName 
                                + '] for Table: ['
                                + TABLE_SCHEMA 
                                + '].['
                                + TABLE_NAME 
                                + '] Was NOT Created Successfully.'
                    FROM   @TB 
                    WHERE  RecId = @WkOrdinal 

                    PRINT @msg
               END

               -- Create extended property.
               SELECT @stmt = ExtendedPropCode 
               FROM   @TB 
               WHERE  RecId = @WkOrdinal 

               EXEC @retval = sp_executesql @stmt = @stmt

               IF ( @retval = 0 )
               BEGIN
                    SELECT @msg = 'Extended Property For Trigger: ['
                                + TriggerName 
                                + '] for Table: ['
                                + TABLE_SCHEMA 
                                + '].['
                                + TABLE_NAME 
                                + '] Created Successfully.'
                    FROM   @TB 
                    WHERE  RecId = @WkOrdinal 

                    PRINT @msg
               END
               ELSE
               BEGIN
                    SELECT @msg = 'Extended Property For Trigger: ['
                                + TriggerName 
                                + '] for Table: ['
                                + TABLE_SCHEMA 
                                + '].['
                                + TABLE_NAME 
                                + '] Was NOT Created Successfully.'
                    FROM   @TB 
                    WHERE  RecId = @WkOrdinal 

                    PRINT @msg
               END
          END
          
          SELECT @WkOrdinal = @WkOrdinal + 1
     END
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'This stored procedure creates data change logging triggers for all tables except the ones that were excluded.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = GenAGUpdTriggers;
GO
